package com.itheima.service.impl;

import com.itheima.domain.entity.Experience;
import com.itheima.mapper.ExperienceMapper;
import com.itheima.service.ExperienceService;
import com.itheima.utils.CategoryUtil;
import com.itheima.utils.SqlUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExperienceServiceImpl implements ExperienceService {
    @Autowired
    private ExperienceMapper experienceMapper;
    @Override
    public List<Experience> total() {
       return experienceMapper.total();
    }

    @Override
    public List<Experience> category(String category) throws SQLException {
        String tb = CategoryUtil.getCategory(category);
        List<Experience> result = query(category, tb);
        return result;
    }
    private static List<Experience> query(String category, String tb) throws SQLException {
        SqlUtil sqlUtil = new SqlUtil();
        String sql = "select * from " + tb + "_experience_analysis where category='" + category + "' order by count desc";
        ResultSet resultSet = sqlUtil.executeQuery(sql);
        List<Experience> experiences = new ArrayList<>();
        while (resultSet.next()) {
            Experience build = Experience.builder()
                    .category(resultSet.getString("category"))
                    .experience(resultSet.getString("experience"))
                    .count(resultSet.getInt("count"))
                    .build();
            experiences.add(build);
        }
        sqlUtil.close();
        return experiences;
    }
}
